<?php
include APPLICATION_PATH . "/models/Report3.php";
/* 
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
class Admin_Report3Controller extends Zend_Controller_Action{
    public function init(){       
        $bootstrap = $this->getInvokeArg("bootstrap");
        $this->aConfig = $bootstrap->getOptions();
        $this->view->aConfig = $this->aConfig;
        $this->model= new Model_Report3();
    }
    public function indexAction(){
       $this->view->controller = $this;
       $this->view->fromdate ="";
       $this->view->todate ="";
       $this->view->product_id = "";
       $this->view->groupproduct_id = "";
       $this->view->agentbuyer_id = "";
       $this->view->agentseller_id = "";
       if($this->getRequest()->isPost()){
            $this->view->fromdate = $_POST["from_date"];
            $this->view->todate = $_POST["to_date"];
            if (isset($_POST['product_id'])){
                $product_id = array();
                foreach ($_POST['product_id'] as $key => $value) {
                    array_push($product_id,$value);                
                }            
                $this->view->product_id = implode(",", $product_id);
            }
            if (isset($_POST['groupproduct_id'])){
                $groupproduct_id = array();
                foreach ($_POST['groupproduct_id'] as $key => $value) {
                    array_push($groupproduct_id,$value);                
                }            
                $this->view->groupproduct_id = implode(",", $groupproduct_id);
            }
            if (isset($_POST['agentbuyer_id'])){
                $agentbuyer_id = array();
                foreach ($_POST['agentbuyer_id'] as $key => $value) {
                    array_push($agentbuyer_id,$value);                
                }            
                $this->view->agentbuyer_id = implode(",", $agentbuyer_id);
            }
            if (isset($_POST['agentseller_id'])){
                $agentseller_id = array();
                foreach ($_POST['agentseller_id'] as $key => $value) {
                    array_push($agentseller_id,$value);                
                }            
                $this->view->agentseller_id = implode(",", $agentseller_id);
            }
       }
       $this->view->productHTML=  GlobalLib::getComboMulti("product_id", "product", "id", "name",NULL,$this->view->product_id,true);
       $this->view->groupproductHTML=  GlobalLib::getComboMulti("groupproduct_id", "product_category", "id", "name",NULL,$this->view->groupproduct_id,true);
       $this->view->agentbuyerHTML=  GlobalLib::getComboMulti("agentbuyer_id", "agent", "id", "name","address",$this->view->agentbuyer_id,true,"where id IN (SELECT buyer.id
                                                                                                                                                        FROM  `order` 
                                                                                                                                                        INNER JOIN  `order_detail` ON order.order_no = order_detail.order_id
                                                                                                                                                        INNER JOIN  `agent` AS buyer ON order.store_id = buyer.id
                                                                                                                                                        INNER JOIN  `agent` AS seller ON order_detail.agent_id = seller.id                        
                                                                                                                                                        LEFT JOIN `product` ON product.id = order_detail.product_id
                                                                                                                                                        LEFT JOIN  `product_category` ON product_category.id = product.product_cat_id
                                                                                                                                                        LEFT JOIN `province` ON province.id = buyer.province_id
                                                                                                                                                        WHERE order_detail.order_status_id = 2 )");
       $this->view->agentsellerHTML=  GlobalLib::getComboMulti("agentseller_id", "agent", "id", "name","address",$this->view->agentseller_id,true,"where id IN (SELECT seller.id
                                                                                                                                                        FROM  `order` 
                                                                                                                                                        INNER JOIN  `order_detail` ON order.order_no = order_detail.order_id
                                                                                                                                                        INNER JOIN  `agent` AS buyer ON order.store_id = buyer.id
                                                                                                                                                        INNER JOIN  `agent` AS seller ON order_detail.agent_id = seller.id                        
                                                                                                                                                        LEFT JOIN `product` ON product.id = order_detail.product_id
                                                                                                                                                        LEFT JOIN  `product_category` ON product_category.id = product.product_cat_id
                                                                                                                                                        LEFT JOIN `province` ON province.id = buyer.province_id
                                                                                                                                                        WHERE order_detail.order_status_id = 2 )");
           
    }
    public function serviceAction(){
        $this->view->controller = $this;        
        $this->_helper->layout->disableLayout();
        $fromdate = $this->_getParam("fromdate","");
        $todate = $this->_getParam("todate","");
        $product_id=$this->_getParam("product_id","");
        $groupproduct_id=$this->_getParam("groupproduct_id","");
        $agentbuyer_id=$this->_getParam("agentbuyer_id","");
        $agentseller_id=$this->_getParam("agentseller_id","");
        $menber   = array();
        foreach ($this->model->fetchAllReport($fromdate, $todate, $product_id, $groupproduct_id, $agentbuyer_id, $agentseller_id) as $items) {
            $order_created=  GlobalLib::viewDate($items->order_created);
            $arr=array(
                'buyer_name'=>$items->buyer_name,
                'seller_name'=>$items->seller_name,
                'product_name'=>$items->product_name,
                'province_name'=>$items->province_name,
                'quantity'=>$items->quantity,
                'charges'=>$items->charges,
                'order_created'=>$order_created
            );
            array_push($menber, $arr);
        }        
        echo json_encode($menber);        
        exit();
    }
    public function exportAction(){
        $this->_helper->layout->disableLayout();
        if($this->getRequest()->isPost()){
           $fromdate=$this->_getParam("fromdate","");
           $todate=$this->_getParam("todate","");
           $product_id=$this->_getParam("product_id","");
            $groupproduct_id=$this->_getParam("groupproduct_id","");
            $agentbuyer_id=$this->_getParam("agentbuyer_id","");
            $agentseller_id=$this->_getParam("agentseller_id","");
            $objPHPExcel = new PHPExcel();
            $objPHPExcel->setActiveSheetIndex(0);
            $rowCount = 4;
            $stt=1;
            $objPHPExcel->getActiveSheet()->setCellValue('A3', "STT");
            $objPHPExcel->getActiveSheet()->setCellValue('B3', "Tên Khách hàng");
            $objPHPExcel->getActiveSheet()->setCellValue('C3', "Khu Vực(Tỉnh)");
            $objPHPExcel->getActiveSheet()->setCellValue('D3', "Khối lượng");
            $objPHPExcel->getActiveSheet()->setCellValue('E3', "Loại Hàng");
            $objPHPExcel->getActiveSheet()->setCellValue('F3', "Thời Điểm");
            $objPHPExcel->getActiveSheet()->setCellValue('G3', "Nguồn(Mua Của ĐL/CH)");
            $objPHPExcel->getActiveSheet()->setCellValue('H3', "Điểm");
            $objPHPExcel->getActiveSheet()->setCellValue('I3', "Ghi Chú");
           
            foreach ($this->model->fetchAllReportExport($fromdate,$todate, $product_id, $groupproduct_id, $agentbuyer_id, $agentseller_id) as $items) {
                $order_created=GlobalLib::viewDate($items->order_created);
                 $t_day = substr($order_created, 0, 2);
                $t_month = substr($order_created, 3, 2);
                $t_year = substr($order_created, 6,4 );
                 // Fixed problems with offsets
                $t_date = PHPExcel_Shared_Date::FormattedPHPToExcel($t_year, $t_month, $t_day);
                $objPHPExcel->getActiveSheet()->setCellValue('A' . $rowCount, $stt);
                $objPHPExcel->getActiveSheet()->setCellValue('B' . $rowCount, $items->buyer_name);
                $objPHPExcel->getActiveSheet()->setCellValue('C' . $rowCount, $items->province_name);
                $objPHPExcel->getActiveSheet()->setCellValue('D' . $rowCount, $items->quantity);
                $objPHPExcel->getActiveSheet()->setCellValue('E' . $rowCount, $items->product_name);  
                if($order_created==''){
                    $objPHPExcel->getActiveSheet()->setCellValue('F' . $rowCount,"" );
                } else {
                    $objPHPExcel->getActiveSheet()->setCellValue('F' . $rowCount,$t_date );
                }
                $objPHPExcel->getActiveSheet()->setCellValue('G' . $rowCount, $items->seller_name);
                $objPHPExcel->getActiveSheet()->setCellValue('H' . $rowCount, $items->charges);
                $rowCount++;
                $stt++;
            }
            $urlimage = GlobalLib::getImageUrl();
            $objPHPExcel->getActiveSheet()->mergeCells('A1:I2');
            $objPHPExcel->getActiveSheet()->setCellValue('A1', "Báo Cáo Bán Hàng Từ Ngày ".$fromdate." Đến Ngày ".$todate);
            $style_alignment = array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("A3:I" . $rowCount)->getAlignment()->applyFromArray($style_alignment);
            $styleArray = array(
                'borders' => array(
                    'allborders' => array(
                        'style' => PHPExcel_Style_Border::BORDER_THIN,
                    ),
                ),
            );
            $styledataArray = array(
                'font' => array(
                    'bold' => true,
                    'color' => array('rgb' => '080808'),
                    'size' => 15,
                    'name' => 'Times New Roman'
            ));
            $styletitleArray=array(
                'font' => array(
                    'bold' => true,
                    'color' => array('rgb' => '080808'),
                    'size' => 10,
                    'name' => 'Times New Roman')
            );
            $objPHPExcel->getActiveSheet()->getStyle('F4:F'. ($rowCount - 1))->getNumberFormat()->setFormatCode("dd-mm-yyyy");
            $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->applyFromArray($style_alignment);
            $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styledataArray);
            $objPHPExcel->getActiveSheet()->getStyle('A3:I3')->applyFromArray($styletitleArray);
            $objPHPExcel->getActiveSheet()->getStyle('A3:I' . ($rowCount - 1))->applyFromArray($styleArray);
            foreach (range('A', 'I') as $columnID) {
                $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
            }
            $filename='BaoCaoBanHangTuNgay'.$fromdate.'DenNgay'.$todate.'.xls';
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename='.$filename);
            header('Cache-Control: max-age=0');
            $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
            $objWriter->save('php://output');
            exit();
        }
    }
}
